# oracle操作类
import cx_Oracle
import json
import datetime


class Oracle_Util:
    user = "pigpos"
    passwd = "pigpos"
    listener = 'TESTZQF.cpchina.cn/TESTZQF'
    conn = cx_Oracle.connect(user, passwd, listener)
    cursor = conn.cursor()

    def __init__(self):

        print('构造函数')

    def select(self, sql) -> json:
        list = []
        self.cursor.execute(sql)
        # 调出数据
        result = self.cursor.fetchall()
        # 字段信息 例如((''))
        col_name = self.cursor.description
        for row in result:
            dict = {}
            for col in range(len(col_name)):
                key = col_name[col][0]
                value = row[col]
                if isinstance(value, datetime.datetime):
                    value = value.strftime('%Y-%m-%d %H:%M:%S')
                dict[key] = value
            list.append(dict)
        # js = json.dumps(list, sort_keys=True, ensure_ascii=False, indent=2, separators=(',', ':'))
        js = json.dumps(list, sort_keys=True, ensure_ascii=False, separators=(',', ':'))
        # self.disconnect()
        return js

    def insert(self, sql, list_param):
        try:
            self.cursor.executemany(sql, list_param)
            self.connect.commit()
            print("插入ok")
        except Exception as e:
            print(e)
        finally:
            self.disconnect()

    def update(self, sql):
        try:
            self.cursor.execute(sql)
            self.connect.commit()

        except Exception as e:
            print(e)
        finally:
            self.disconnect()

    def delete(self, sql):
        try:
            self.cursor.execute(sql)
            self.connect.commit()
            print("delete ok")
        except Exception as e:
            print(e)
        finally:
            self.disconnect()

    # 关闭连接，释放资源
    def disconnect(self):
        self.cursor.close()
        self.conn.close()
